package org.df.ulab.util.db;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @author Daniel Filgueiras
 * @since 19/06/2011
 */
public abstract class DBAccess {

	protected DataSource ds;
	private MapListHandler mlh = new MapListHandler();
	private QueryRunner qr;
	
	private ScalarHandler scalarHandler = new ScalarHandler();
	protected final String serverName;
	protected final String userName;
	protected final String password;
	protected final String databaseName;

	private static final Logger logger = LoggerFactory.getLogger(DBAccess.class);

	public DBAccess(String serverName, String databaseName, String userName,
			String password) {
		this.databaseName = databaseName;
		this.password = password;
		this.serverName = serverName;
		this.userName = userName;
	}

	protected abstract void makeDS();
	public abstract boolean testConnection();

	private void makeQueryRunner() {
		if (qr == null) {
			makeDS();
			qr = new QueryRunner();
		}
	}

	@SuppressWarnings("deprecation")
	public final List<Map<String, Object>> executeListMapQuery(String query,
			Object... params) {
		this.makeQueryRunner();

		Connection c = null;		
		try {
			c = ds.getConnection();
			return (List<Map<String, Object>>) qr.query(c, query, params, mlh);
			
		} catch (SQLException e) {
			logger.error("Executando query", e);
			return null;
		} finally {
			if (c != null) {
				try {
					c.close();
				} catch (SQLException sqle) {
					
				}
			}
		}
	}
	
	
	@SuppressWarnings({ "deprecation", "unchecked" })
	public final <T> List<T> executeListScalarQuery(String query,
			Object... params) {
		this.makeQueryRunner();
		
		Connection c = null;		
		try {
			c = ds.getConnection();
			return (List<T>) qr.query(c, query, params,
					new ColumnListHandler());
			
		} catch (SQLException e) {
			logger.error("Executando query", e);
			return Collections.emptyList();
		} finally {
			if (c != null) {
				try {
					c.close();
				} catch (SQLException sqle) {
					
				}
			}
		}
	}
	
	@SuppressWarnings({ "unchecked", "deprecation" })
	public final <T> T executeScalarQuery(String query,
			Object... params) {
		this.makeQueryRunner();

		Connection c = null;		
		try {
			c = ds.getConnection();
			return (T) qr.query(c, query, params, scalarHandler);
			
		} catch (SQLException e) {
			logger.error("Executando query", e);
			return null;
		} finally {
			if (c != null) {
				try {
					c.close();
				} catch (SQLException sqle) {
					
				}
			}
		}
	}
	
	public final Map<String, Object> executeMapQuery(String query,
			Object... params) {
		List<Map<String, Object>> lista = executeListMapQuery(query, params);
		
		if (lista.size() > 0)
			return lista.get(0);
		
		return Collections.emptyMap();
	}

	public final List<Map<String, Object>> executeListMapQuery(String query) {
		return this.executeListMapQuery(query, (Object[]) null);
	}
		
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public final List<Object> executeListBeanQuery(String ddl, Class clazz, Object... objs) {
		
		this.makeQueryRunner();
		
		Connection c = null;		
		try {
			c = ds.getConnection();
			return (List<Object>) qr.query(c, ddl, new BeanListHandler(clazz), objs);
		} catch (SQLException e) {
			logger.error("Executando query", e);
			return Collections.emptyList();
		} finally {
			if (c != null) {
				try {
					c.close();
				} catch (SQLException sqle) {
					
				}
			}
		}
	}
	
	public final boolean executeUpdate(String ddl, Object... params) throws SQLUpdateException {
		makeQueryRunner();
		
		Connection c = null;
		try {
			c = ds.getConnection();
			qr.update(c,ddl, params);
			return true;
			
		} catch (SQLException e) {
			logger.error("Executando query", e);
			SQLException sqle = e;
			while (sqle.getNextException() != null) {
				sqle = sqle.getNextException();
			}
			
			if (e.getErrorCode() == 229) {
				throw new SQLUpdateException(sqle);
			}
			return false;
		} finally {
			if (c != null) {
				try {
					c.close();
				} catch (SQLException sqle) {
				}
			}
			;
		}
	}
	
	public final boolean executeUpdate(String ddl, Object[][] params) {
		makeQueryRunner();
		
		Connection c = null;
		try {
			c = ds.getConnection();
			qr.batch(c,ddl, params);
			return true;
		} catch (Exception e) {
			logger.error("Executando query", e);
			return false;
		} finally {
			if (c != null) {
				try {
					c.close();
				} catch (SQLException sqle) {
				}
			}
			;
		}
	}
}